package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IVehicleInfo;
import youleche.com.model.MVehicleInfo;
/**
 * 车辆信息数据访问层
 * @author Administrator
 *
 */
public class DVehicelInfo implements IVehicleInfo{
	/**
	 * 添加车辆信息
	 * @param con 数据库连接
	 * @param vehicleInfo 车辆信息
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addVehicleInfo(Connection con, MVehicleInfo vehicleInfo)
			throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT vehicleInfo(" +
					"vehicleId,vehicleName,vehicleType,vehicleColor,batterySize," +
					"electricitySize,workLength,wheelInternalLength,addTime,modifyTime," +
					"vehicleModelType,maxSpeed,maxWeight,createTime,price," +
					"sellerId,isDelete,imageId,vehicleDesc," +
					"A1,A2,A3,A4,A5," +
					"A6,A7,A8,A9,A10," +
					"A11,A12,A13,A14,A15," +
					"A16,A17,A18,A19,A20," +
					"A21,A22,A23,A24,A25," +
					"A26,A27,A28,A29,A30," +
					"linkMan,linkPhoneNum) " +
					"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			
			pre = con.prepareStatement(sql);
			pre.setObject(1, vehicleInfo.getVehicleId());
			pre.setObject(2, vehicleInfo.getVehicleName());
			pre.setObject(3, vehicleInfo.getVehicleType());
			pre.setObject(4, vehicleInfo.getVehicleColor());
			pre.setObject(5, vehicleInfo.getBatterySize());
			pre.setObject(6, vehicleInfo.getElectricitySize());
			pre.setObject(7, vehicleInfo.getWorkLength());
			pre.setObject(8, vehicleInfo.getWheelInternalLength());
			pre.setObject(9, vehicleInfo.getAddTime());
			pre.setObject(10, vehicleInfo.getModifyTime());
			pre.setObject(11, vehicleInfo.getVehicleModelType());
			pre.setObject(12, vehicleInfo.getMaxSpeed());
			pre.setObject(13, vehicleInfo.getMaxWeight());
			pre.setObject(14, vehicleInfo.getCreateTime());
			pre.setObject(15, vehicleInfo.getPrice());
			pre.setObject(16, vehicleInfo.getSellerId());
			pre.setObject(17, vehicleInfo.getIsDelete());
			pre.setObject(18, vehicleInfo.getImageId());
			pre.setObject(19, vehicleInfo.getVehicleDesc());
			pre.setObject(20, vehicleInfo.getA1());
			pre.setObject(21, vehicleInfo.getA2());
			pre.setObject(22, vehicleInfo.getA3());
			pre.setObject(23, vehicleInfo.getA4());
			pre.setObject(24, vehicleInfo.getA5());
			pre.setObject(25, vehicleInfo.getA6());
			pre.setObject(26, vehicleInfo.getA7());
			pre.setObject(27, vehicleInfo.getA8());
			pre.setObject(28, vehicleInfo.getA9());
			pre.setObject(29, vehicleInfo.getA10());
			pre.setObject(30, vehicleInfo.getA11());
			pre.setObject(31, vehicleInfo.getA12());
			pre.setObject(32, vehicleInfo.getA13());
			pre.setObject(33, vehicleInfo.getA14());
			pre.setObject(34, vehicleInfo.getA15());
			pre.setObject(35, vehicleInfo.getA16());
			pre.setObject(36, vehicleInfo.getA17());
			pre.setObject(37, vehicleInfo.getA18());
			pre.setObject(38, vehicleInfo.getA19());
			pre.setObject(39, vehicleInfo.getA20());
			pre.setObject(40, vehicleInfo.getA21());
			pre.setObject(41, vehicleInfo.getA22());
			pre.setObject(42, vehicleInfo.getA23());
			pre.setObject(43, vehicleInfo.getA24());
			pre.setObject(44, vehicleInfo.getA25());
			pre.setObject(45, vehicleInfo.getA26());
			pre.setObject(46, vehicleInfo.getA27());
			pre.setObject(47, vehicleInfo.getA28());
			pre.setObject(48, vehicleInfo.getA29());
			pre.setObject(49, vehicleInfo.getA30());
			pre.setObject(50, vehicleInfo.getLinkMan());
			pre.setObject(51, vehicleInfo.getLinkPhoneNum());
			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 通过ID得到车辆
	 * @param con 数据库连接
	 * @param id ID
	 * @return 返回车辆信息
	 * @throws Exception 异常
	 */
	@Override
	public MVehicleInfo getVehicleInfoById(Connection con, String id)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"vehicleId,vehicleName,vehicleType,vehicleColor,batterySize," +
					"electricitySize,workLength,wheelInternalLength,addTime,modifyTime," +
					"vehicleModelType,maxSpeed,maxWeight,createTime,price," +
					"sellerId,isDelete,imageId,vehicleDesc," +
					"A1,A2,A3,A4,A5," +
					"A6,A7,A8,A9,A10," +
					"A11,A12,A13,A14,A15," +
					"A16,A17,A18,A19,A20," +
					"A21,A22,A23,A24,A25," +
					"A26,A27,A28,A29,A30," +
					"linkMan,linkPhoneNum " +
					"FROM vehicleInfo " +
					"where vehicleId='" + id + "' and isDelete=0";
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MVehicleInfo vehicle = null;
			while(rs.next()) {
				vehicle = new MVehicleInfo();
				vehicle.setVehicleId(rs.getString(1));
				vehicle.setVehicleName(rs.getString(2));
				vehicle.setVehicleType(rs.getString(3));
				vehicle.setVehicleColor(rs.getString(4));
				vehicle.setBatterySize(rs.getString(5));
				vehicle.setElectricitySize(rs.getString(6));
				vehicle.setWorkLength(rs.getString(7));
				vehicle.setWheelInternalLength(rs.getString(8));
				vehicle.setAddTime(rs.getDate(9));
				vehicle.setModifyTime(rs.getDate(10));
				vehicle.setVehicleModelType(rs.getString(11));
				vehicle.setMaxSpeed(rs.getString(12));
				vehicle.setMaxWeight(rs.getString(13));
				vehicle.setCreateTime(rs.getDate(14));
				vehicle.setPrice(rs.getString(15));
				vehicle.setImageId(rs.getString(18));
				vehicle.setVehicleDesc(rs.getString(19));
				vehicle.setA1(rs.getString(20));
				vehicle.setA2(rs.getString(21));
				vehicle.setA3(rs.getString(22));
				vehicle.setA4(rs.getString(23));
				vehicle.setA5(rs.getString(24));
				vehicle.setA6(rs.getString(25));
				vehicle.setA7(rs.getString(26));
				vehicle.setA8(rs.getString(27));
				vehicle.setA9(rs.getString(28));
				vehicle.setA10(rs.getString(29));
	
				vehicle.setA11(rs.getString(30));
				vehicle.setA12(rs.getString(31));
				vehicle.setA13(rs.getString(32));
				vehicle.setA14(rs.getString(33));
				vehicle.setA15(rs.getString(34));
				vehicle.setA16(rs.getString(35));
				vehicle.setA17(rs.getString(36));
				vehicle.setA18(rs.getString(37));
				vehicle.setA19(rs.getString(38));
				vehicle.setA20(rs.getString(39));
				
				vehicle.setA21(rs.getString(40));
				vehicle.setA22(rs.getString(41));
				vehicle.setA23(rs.getString(42));
				vehicle.setA24(rs.getString(43));
				vehicle.setA25(rs.getString(44));
				vehicle.setA26(rs.getString(45));
				vehicle.setA27(rs.getString(46));
				vehicle.setA28(rs.getString(47));
				vehicle.setA29(rs.getString(48));
				vehicle.setA30(rs.getString(49));
				
				vehicle.setLinkMan(rs.getString(50));
				vehicle.setLinkPhoneNum(rs.getString(51));
			}
			
			return vehicle;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取首页基础加载的数据
	 * @param con 数据库连接
	 * @param count 获取的条数
	 * @return 返回车辆信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleInfo> getVehicleInfoListForHome(Connection con,
			int count) throws Exception{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select vehicleId,addTime,vehicleName,price,batterySize,electricitySize,addTime" +
					",A1,workLength,maxSpeed,A2,A6,A7," +
					"imageId" +
					" from vehicleInfo order by addTime desc limit "+count+";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MVehicleInfo> list = new LinkedList<MVehicleInfo>();
			MVehicleInfo vehicle = null;
			while(rs.next()) {
				vehicle = new MVehicleInfo();
				vehicle.setVehicleId(rs.getString(1));
				vehicle.setAddTime(rs.getDate(2));
				vehicle.setVehicleName(rs.getString(3));
				vehicle.setPrice(rs.getString(4));
				vehicle.setBatterySize(rs.getString(5));
				vehicle.setElectricitySize(rs.getString(6));
				vehicle.setAddTime(rs.getDate(7));
				vehicle.setA1(rs.getString(8));
				vehicle.setWorkLength(rs.getString(9));
				vehicle.setMaxSpeed(rs.getString(10));
				vehicle.setA2(rs.getString(11));
				vehicle.setA6(rs.getString(12));
				vehicle.setA7(rs.getString(13));
				vehicle.setImageId(rs.getString(14));
				
				list.add(vehicle);
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
	
	/**
	 * 判断是否添加了相似的车辆信息（针对同一商户）
	 * @param con 数据库连接
	 * @param vehicleInfo 车辆信息
	 * @return 返回是否添加了相似的车辆信息 true  是
	 * @throws Exception 异常
	 */
	@Override
	public String isSimilarityForVehicleInfo(Connection con,
			MVehicleInfo vehicleInfo) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			
		String sql = "select vehicleId " +
				"from vehicleInfo " +
				"where isdelete=0 " +
				"and sellerId = '"+vehicleInfo.getSellerId()+"' " +
				"and (linkPhoneNum='"+vehicleInfo.getLinkPhoneNum()+"' or vehicleName='"+vehicleInfo.getVehicleName()+"') limit 1";
			return "";
		}finally{
			
		}
	}
	
	/**
	 * 通过id获取车辆信息的访问量
	 * @param con 数据库连接
	 * @param id 车辆id
	 * @return 返回访问量
	 * @throws Exception 异常
	 */
	@Override
	public int getCountById(Connection con, String id) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select a3 from vehicleinfo where vehicleId='"+id+"' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			String count = "";
			while(rs.next()) {
				count = rs.getString(1);
				break;
			}
			
			if(count != null){
				return Integer.parseInt(count);
			}
			
			return 0;
	
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过车辆信息ID更新访问量
	 * @param con 数据库连接
	 * @param id 车辆信息id
	 * @return 返回车辆信息id
	 * @throws Exception 异常
	 */
	@Override
	public int updateCountById(Connection con, String id, String count) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update vehicleInfo set a3='"+count+"' where vehicleId='"+id+"' limit 1;";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 获取品牌总数
	 * @param con 数据库连接
	 * @return 返回品牌总数
	 * @throws Exception 异常
	 */
	@Override
	public int getVehicleNameForCount(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(vehicleName) from (select vehicleName from vehicleInfo group by vehicleName) as vehicleNameInfo";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getInt(1);
				}
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取分页数据
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 结束索引
	 * @return 返回分页数据
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleInfo> getVehicleInfoList(Connection con,
			String startIndex, String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select vehicleId,addTime,vehicleName,price,batterySize,electricitySize,addTime" +
			",A1,workLength,maxSpeed,A2,A6" +
			",A3,linkMan,A7,imageId" +
			" from vehicleInfo where isdelete=0 order by addTime desc limit "+startIndex+", "+endIndex+";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MVehicleInfo> list = new LinkedList<MVehicleInfo>();
			MVehicleInfo vehicle = null;
			while(rs.next()) {
				vehicle = new MVehicleInfo();
				vehicle.setVehicleId(rs.getString(1));
				vehicle.setAddTime(rs.getDate(2));
				vehicle.setVehicleName(rs.getString(3));
				vehicle.setPrice(rs.getString(4));
				vehicle.setBatterySize(rs.getString(5));
				vehicle.setElectricitySize(rs.getString(6));
				vehicle.setAddTime(rs.getDate(7));
				vehicle.setA1(rs.getString(8));
				vehicle.setWorkLength(rs.getString(9));
				vehicle.setMaxSpeed(rs.getString(10));
				vehicle.setA2(rs.getString(11));
				vehicle.setA6(rs.getString(12));
				vehicle.setA3(rs.getString(13));
				vehicle.setLinkMan(rs.getString(14));
				vehicle.setA7(rs.getString(15));
				vehicle.setImageId(rs.getString(16));
				list.add(vehicle);
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过ID删除删除车辆信息
	 * @param con 数据库连接
	 * @param id id
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int deleteVehicleInfoById(Connection con, String id)
			throws Exception {
		PreparedStatement ps = null;
		try{
			//String sql = "update vehicleInfo set isdelete = 1 where isdelete=0 and vehicleid='"+id+"' limit 1;";
			String sql = "delete from vehicleInfo where isdelete=0 and vehicleid='"+id+"' limit 1;";
			
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 获取车辆信息总数
	 * @param con 数据库连接
	 * @return 车辆信息总数
	 * @throws Exception 异常
	 */
	@Override
	public int getCount(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(vehicleId) from vehicleInfo where isdelete=0";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			int result = 0;
			if(rs != null){
				while(rs.next()){
					result = rs.getInt(1);
					break;
				}
			}
			return result;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过商家ID得到车辆信息
	 * @param con 数据库连接
	 * @param sellerId ID
	 * @return 返回车辆信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleInfo> getVehicleInfoBySellerId(Connection con,
			String sellerId, String startIndex, String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select vehicleId,addTime,vehicleName,price,batterySize,electricitySize,addTime" +
			",A1,workLength,maxSpeed,A2,A6" +
			",A3,linkMan" +
			" from vehicleInfo where isdelete=0 and sellerId='"+sellerId+"' order by addTime desc limit "+startIndex+", "+endIndex+";";
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MVehicleInfo> list = new LinkedList<MVehicleInfo>();
			MVehicleInfo vehicle = null;
			while(rs.next()) {
				vehicle = new MVehicleInfo();
				vehicle.setVehicleId(rs.getString(1));
				vehicle.setAddTime(rs.getDate(2));
				vehicle.setVehicleName(rs.getString(3));
				vehicle.setPrice(rs.getString(4));
				vehicle.setBatterySize(rs.getString(5));
				vehicle.setElectricitySize(rs.getString(6));
				vehicle.setAddTime(rs.getDate(7));
				vehicle.setA1(rs.getString(8));
				vehicle.setWorkLength(rs.getString(9));
				vehicle.setMaxSpeed(rs.getString(10));
				vehicle.setA2(rs.getString(11));
				vehicle.setA6(rs.getString(12));
				vehicle.setA3(rs.getString(13));
				vehicle.setLinkMan(rs.getString(14));
				list.add(vehicle);
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
	
	/**
	 * 根据商户Id获取发布信息的总数量
	 * @param con 数据库连接
	 * @param sellerId 商户id
	 * @return 返回发布信息的总数量
	 * @throws Exception
	 */
	@Override
	public int getCountBySellerId(Connection con, String sellerId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(vehicleId) from vehicleInfo where isdelete=0 and sellerId='"+sellerId+"'";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			int result = 0;
			if(rs != null){
				while(rs.next()){
					result = rs.getInt(1);
					break;
				}
			}
			
			return result;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
	
	/**
	 * 搜索获取分页数据
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 结束索引
	 * @param condition 搜索条件
	 * @return 返回分页数据
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleInfo> getVehicleInfoListBySearch(Connection con,
			String startIndex, String endIndex, String condition)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select vehicleId,addTime,vehicleName,price,batterySize,electricitySize,addTime" +
			",A1,workLength,maxSpeed,A2,A6" +
			",A3,linkMan,A7" +
			" from vehicleInfo where isdelete=0 and vehicleName like '%"+condition+"%' order by addTime desc limit "+startIndex+", "+endIndex+";";
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MVehicleInfo> list = new LinkedList<MVehicleInfo>();
			MVehicleInfo vehicle = null;
			while(rs.next()) {
				vehicle = new MVehicleInfo();
				vehicle.setVehicleId(rs.getString(1));
				vehicle.setAddTime(rs.getDate(2));
				vehicle.setVehicleName(rs.getString(3));
				vehicle.setPrice(rs.getString(4));
				vehicle.setBatterySize(rs.getString(5));
				vehicle.setElectricitySize(rs.getString(6));
				vehicle.setAddTime(rs.getDate(7));
				vehicle.setA1(rs.getString(8));
				vehicle.setWorkLength(rs.getString(9));
				vehicle.setMaxSpeed(rs.getString(10));
				vehicle.setA2(rs.getString(11));
				vehicle.setA6(rs.getString(12));
				vehicle.setA3(rs.getString(13));
				vehicle.setLinkMan(rs.getString(14));
				vehicle.setA7(rs.getString(15));
				list.add(vehicle);
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 更新车辆信息的首页图片地址
	 * @param con 数据库连接
	 * @param imageId 图片id
	 * @param vehicleId 车辆id
	 * @return 返回更新影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int updateImageIdByVehicleId(Connection con, String imageId,
			String vehicleId) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update vehicleInfo set imageId ='"+imageId+"' where isdelete=0 and vehicleId='"+vehicleId+"' limit 1;";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 用户置顶车辆报价信息
	 * @param con 数据库连接
	 * @param vehicleId 车辆ID
	 * @return 返回影响的行数
	 * @throws Exception
	 */
	@Override
	public int topVehicleInfoById(Connection con, String vehicleId)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update vehicleInfo set addTime = now() where vehicleId='"+vehicleId+"' and isDelete='0' limit 1;";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally
		{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 更新车辆信息
	 * @param con 数据库连接
	 * @param vechileInfo 车辆信息
	 * @return 返回更新影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int updateVehicleInfoById(Connection con, MVehicleInfo vechileInfo)
			throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	/**
	 * 得到该商户今天发布报价信息的条数
	 * @param con 数据库连接
	 * @param sellerId 商户ID
	 * @return 返回报价信息条数
	 * @throws Exception 异常
	 */
	@Override
	public String getVehicleInfoCountForTodayBySellerId(Connection con,
			String sellerId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(vehicleId) from vehicleInfo where isdelete=0 and sellerId = '"+sellerId+"' and  DATE_FORMAT(addTime, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d')";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs !=null){
				while(rs.next()){
					return rs.getString(1);
				}
			}
			
			return "0";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
}
